﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient; 
using System.Data;  
using Stone.Library.DB;
using Stone.DataContracts;

namespace CourseArraging.DAO
{
    public class basicDAO
    {
        #region 分校
        #region 查询分校信息

        public DepEntity getDepEntity(int id)
        {
            string[][] tmp = getDepinfo(id);
            DepEntity de = new DepEntity();
            de.dep_info_id = int.Parse(tmp[0][0]);
            de.dep_info_name = tmp[0][1];
            de.dep_info_address = tmp[0][2];
            de.dep_info_phone = tmp[0][3];
            return de;
        }

        public string[][] getDepinfo()
        {
            return getDepinfo(0);
        }
        public string[][] getDepinfo(int dep_id)
        {
            return getDepinfo(dep_id, null, null, null);
        }
        public string[][] getDepinfo(string dep_phone)
        {
            return getDepinfo(0, null, null, dep_phone);
        }
        public string[][] getDepinfo(int dep_id, string dep_name,string dep_address,string dep_phone)
        {
            StringBuilder sb = new StringBuilder("select dep_info_id 编号,dep_info_name 分校,dep_info_address 地址,dep_info_phone 电话")
                .Append(" from dep_info where 1=1 ");
            List<SqlParameter> sqlParamsList=new List<SqlParameter>();
            if (dep_id!=0)
            {
                sb.Append(" and dep_info_id=@dep_info_id ");
                sqlParamsList.Add(new SqlParameter("@dep_info_id", dep_id));
            }
            if (!string.IsNullOrEmpty(dep_name))
            {
                sb.Append(" and dep_info_name=@dep_info_name ");
                sqlParamsList.Add(new SqlParameter("@dep_info_name", dep_name));
            }
            if (!string.IsNullOrEmpty(dep_address))
            {
                sb.Append(" and dep_info_address like %@dep_info_address% ");
                sqlParamsList.Add(new SqlParameter("@dep_info_address", dep_address));
            }
            if (!string.IsNullOrEmpty(dep_phone))
            {
                sb.Append(" and dep_info_phone like %@dep_info_phone% ");
                sqlParamsList.Add(new SqlParameter("@dep_info_phone", dep_phone));
            }
            return DBExecuteUtil.querySqlArray(sb.ToString(), sqlParamsList.ToArray());
        }

        public DataTable getDepinfo4table()
        {
            return getDepinfo4table(0);
        }
        public DataTable getDepinfo4table(int dep_id)
        {
            return getDepinfo4table(dep_id, null, null, null);
        }
        public DataTable getDepinfo4table(string dep_phone)
        {
            return getDepinfo4table(0, null, null, dep_phone);
        }
        public DataTable getDepinfo4table(int dep_id, string dep_name, string dep_address, string dep_phone)
        {
            StringBuilder sb = new StringBuilder("select dep_info_id 编号,dep_info_name 分校,dep_info_address 地址,dep_info_phone 电话")
                .Append(" from dep_info where 1=1 ");
            List<SqlParameter> sqlParamsList = new List<SqlParameter>();
            if (dep_id != 0)
            {
                sb.Append(" and dep_info_id=@dep_info_id ");
                sqlParamsList.Add(new SqlParameter("@dep_info_id", dep_id));
            }
            if (!string.IsNullOrEmpty(dep_name))
            {
                sb.Append(" and dep_info_name=@dep_info_name ");
                sqlParamsList.Add(new SqlParameter("@dep_info_name", dep_name));
            }
            if (!string.IsNullOrEmpty(dep_address))
            {
                sb.Append(" and dep_info_address like %@dep_info_address% ");
                sqlParamsList.Add(new SqlParameter("@dep_info_address", dep_address));
            }
            if (!string.IsNullOrEmpty(dep_phone))
            {
                sb.Append(" and dep_info_phone like %@dep_info_phone% ");
                sqlParamsList.Add(new SqlParameter("@dep_info_phone", dep_phone));
            }
            return DBExecuteUtil.querySqlTable(sb.ToString(), sqlParamsList.ToArray());
        }

        public bool getEqualDep(string dep_name)
        {
            string[][] tmp = getDepinfo(0, dep_name, null, null);
            if (tmp==null||tmp.Length == 0)
                return false;
            else
                return true;
        }

        #endregion

        #region 操作分校信息

        public void addDepInfo(DepEntity dep)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("insert into Dep_info(Dep_info_name,dep_info_phone,dep_info_address) ");
            sb.Append("values (@dep_info_name,dep_info_phone,dep_info_address)");
            SqlParameter[] sqlParams = new SqlParameter[]{
                new SqlParameter("@Dep_name",dep.dep_info_name),
                new SqlParameter("@dep_info_address",dep.dep_info_address),
                new SqlParameter("@dep_info_phone",dep.dep_info_phone)
            };
            DBExecuteUtil.executeSqlWithPar(sb.ToString(), sqlParams);
        }

        public void updateDepinfo(DepEntity dep)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("update Dep_info set dep_info_name=@dep_info_name,dep_info_phone=@dep_info_phone,dep_info_address=@dep_info_address ");
            sb.Append(" where dep_info_id=@dep_info_id");
            SqlParameter[] sqlParams = new SqlParameter[]{
                new SqlParameter("@dep_info_id",dep.dep_info_id),
                new SqlParameter("@dep_info_name",dep.dep_info_name),
                new SqlParameter("@dep_info_phone",dep.dep_info_phone),
                new SqlParameter("@dep_info_address",dep.dep_info_address)
            };
            DBExecuteUtil.executeSqlWithPar(sb.ToString(), sqlParams);
        }

        public void deleteDepInfo(string Dep_name)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("insert into Dep_info(Dep_info_name) values (@Dep_name)");
            SqlParameter[] sqlParams = new SqlParameter[]{
                new SqlParameter("@Dep_name",Dep_name)
            };
            DBExecuteUtil.executeSqlWithPar(sb.ToString(), sqlParams);
        }

        #endregion

        #endregion

        #region 课程

        #region 查询课程信息

        public string[][] getCourseInfo()
        {
            return getCourseInfo(0, null);
        }
        public string[][] getCourseInfo(int course_id)
        {
            return getCourseInfo(course_id, null);
        }
        public string[][] getCourseInfo(string course_name)
        {
            return getCourseInfo(0, course_name);
        }
        public string[][] getCourseInfo(int course_id, string course_name)
        {
            StringBuilder sb = new StringBuilder("select course_info_id 编号,course_info_name 课程 ")
                .Append(" from course_info where 1=1 ");
            List<SqlParameter> sqlParamsList = new List<SqlParameter>();
            if (course_id != 0)
            {
                sb.Append(" and course_info_id=@course_info_id ");
                sqlParamsList.Add(new SqlParameter("@course_info_id", course_id));
            }
            if (!string.IsNullOrEmpty(course_name))
            {
                sb.Append(" and course_info_name=@course_info_name ");
                sqlParamsList.Add(new SqlParameter("@course_info_name", course_name));
            }
            return DBExecuteUtil.querySqlArray(sb.ToString(), sqlParamsList.ToArray());
        }

        public DataTable getCourseInfo4table()
        {
            return getCourseInfo4table(0, null);
        }
        public DataTable getCourseInfo4table(int course_id)
        {
            return getCourseInfo4table(course_id, null);
        }
        public DataTable getCourseInfo4table(string course_name)
        {
            return getCourseInfo4table(0, course_name);
        }
        public DataTable getCourseInfo4table(int course_id, string course_name)
        {
            StringBuilder sb = new StringBuilder("select course_info_id 编号,course_info_name 课程 ")
                .Append(" from course_info where 1=1 ");
            List<SqlParameter> sqlParamsList = new List<SqlParameter>();
            if (course_id != 0)
            {
                sb.Append(" and course_info_id=@course_info_id ");
                sqlParamsList.Add(new SqlParameter("@course_info_id", course_id));
            }
            if (!string.IsNullOrEmpty(course_name))
            {
                sb.Append(" and course_info_name=@course_info_name ");
                sqlParamsList.Add(new SqlParameter("@course_info_name", course_name));
            }
            return DBExecuteUtil.querySqlTable(sb.ToString(), sqlParamsList.ToArray());
        }

        public bool getEqualCourse(string course_name)
        {
            string[][] tmp=getCourseInfo(course_name);
            if (tmp==null||tmp.Length == 0)
                return false;
            else
                return true;
        }

        #endregion

        #region 操作课程信息

        public void addCourseInfo(string course_name)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("insert into course_info(course_info_name) values (@course_name)");
            SqlParameter[] sqlParams = new SqlParameter[]{
                new SqlParameter("@course_name",course_name)
            };
            DBExecuteUtil.executeSqlWithPar(sb.ToString(), sqlParams);
        }

        public void deleteCourseInfo(string course_name)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("insert into course_info(course_info_name) values (@course_name)");
            SqlParameter[] sqlParams = new SqlParameter[]{
                new SqlParameter("@course_name",course_name)
            };
            DBExecuteUtil.executeSqlWithPar(sb.ToString(), sqlParams);
        }

        public void updateCourseinfo(int id, string content)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("update course_info set course_info_name=@course_info_name ");
            sb.Append(" where course_info_id=@course_info_id");
            SqlParameter[] sqlParams = new SqlParameter[]{
                new SqlParameter("@course_info_name",content),
                new SqlParameter("@course_info_id",id)
            };
            DBExecuteUtil.executeSqlWithPar(sb.ToString(), sqlParams);
        }

        #endregion

        #endregion

        #region 年级

        #region 查询年级信息

        public string[][] getGradeInfo()
        {
            return getGradeInfo(0, null);
        }
        public string[][] getGradeInfo(int grade_id)
        {
            return getGradeInfo(grade_id,null);
        }
        public string[][] getGradeInfo(string grade_name)
        {
            return getGradeInfo(0,grade_name);
        }
        public string[][] getGradeInfo(int grade_id, string grade_name)
        {
            StringBuilder sb = new StringBuilder("select grade_info_id 编号,grade_info_name 年级 ")
                .Append(" from grade_info where 1=1 ");
            List<SqlParameter> sqlParamsList = new List<SqlParameter>();
            if (grade_id != 0)
            {
                sb.Append(" and grade_info_id=@grade_info_id ");
                sqlParamsList.Add(new SqlParameter("@grade_info_id", grade_id));
            }
            if (!string.IsNullOrEmpty(grade_name))
            {
                sb.Append(" and grade_info_name=@grade_info_name ");
                sqlParamsList.Add(new SqlParameter("@grade_info_name", grade_name));
            }
            return DBExecuteUtil.querySqlArray(sb.ToString(), sqlParamsList.ToArray());
        }

        public DataTable getGradeInfo4table()
        {
            return getGradeInfo4table(0, null);
        }
        public DataTable getGradeInfo4table(int grade_id)
        {
            return getGradeInfo4table(grade_id, null);
        }
        public DataTable getGradeInfo4table(string grade_name)
        {
            return getGradeInfo4table(0, grade_name);
        }
        public DataTable getGradeInfo4table(int grade_id, string grade_name)
        {
            StringBuilder sb = new StringBuilder("select grade_info_id 编号,grade_info_name 年级  ")
                .Append(" from grade_info where 1=1 ");
            List<SqlParameter> sqlParamsList = new List<SqlParameter>();
            if (grade_id != 0)
            {
                sb.Append(" and grade_info_id=@grade_info_id ");
                sqlParamsList.Add(new SqlParameter("@grade_info_id", grade_id));
            }
            if (!string.IsNullOrEmpty(grade_name))
            {
                sb.Append(" and grade_info_name=@grade_info_name ");
                sqlParamsList.Add(new SqlParameter("@grade_info_name", grade_name));
            }
            return DBExecuteUtil.querySqlTable(sb.ToString(), sqlParamsList.ToArray());
        }

        public bool getEqualGrade(string Grade_name)
        {
            string[][] tmp = getGradeInfo(Grade_name);
            if (tmp==null||tmp.Length == 0)
                return false;
            else
                return true;
        }
        #endregion

        #region 操作年级信息

        public void addGradeInfo(string Grade_name)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("insert into Grade_info(Grade_info_name) values (@Grade_name)");
            SqlParameter[] sqlParams = new SqlParameter[]{
                new SqlParameter("@Grade_name",Grade_name)
            };
            DBExecuteUtil.executeSqlWithPar(sb.ToString(), sqlParams);
        }

        public void deleteGradeInfo(string Grade_name)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("insert into Grade_info(Grade_info_name) values (@Grade_name)");
            SqlParameter[] sqlParams = new SqlParameter[]{
                new SqlParameter("@Grade_name",Grade_name)
            };
            DBExecuteUtil.executeSqlWithPar(sb.ToString(), sqlParams);
        }

        public void updateGradeInfo(int id, string content)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("update Grade_info set Grade_info_name=@Grade_name");
            sb.Append(" where grade_info_id=@grade_info_id ");
            SqlParameter[] sqlParams = new SqlParameter[]{
                new SqlParameter("@Grade_name",content),
                new SqlParameter("@grade_info_id",id)
            };
            DBExecuteUtil.executeSqlWithPar(sb.ToString(), sqlParams);
        }

        #endregion

        #endregion
         
        #region 权限 

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public RightEntity RightEntity(int yh_right_id)
        {
            RightEntity re = new RightEntity();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select yh_right_id,yh_right_name ");
            strSql.Append(" FROM yh_right ");
            strSql.Append(" where yh_right_id=@yh_right_id ");
            SqlParameter[] parameters = {
					new SqlParameter("@yh_right_id", SqlDbType.Int,4)};
            parameters[0].Value = yh_right_id;

            DataTable dt = DBExecuteUtil.querySqlTable(strSql.ToString(), parameters);
            if (dt.Rows.Count > 0)
            {
                if (dt.Rows[0]["yh_right_id"].ToString() != "")
                {
                    yh_right_id = int.Parse(dt.Rows[0]["yh_right_id"].ToString());
                }
                re.yh_right_name = dt.Rows[0]["yh_right_name"].ToString();
            }
            return re;
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(RightEntity re)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into yh_right(");
            strSql.Append("yh_right_name)");
            strSql.Append(" values (");
            strSql.Append("@yh_right_name)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@yh_right_name", SqlDbType.VarChar,50)};
            parameters[0].Value = re.yh_right_name;

            object obj =  DBExecuteUtil.querySqlTable(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 1;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public void Update(RightEntity re)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update yh_right set ");
            strSql.Append("yh_right_name=@yh_right_name");
            strSql.Append(" where yh_right_id=@yh_right_id ");
            SqlParameter[] parameters = {
					new SqlParameter("@yh_right_id", SqlDbType.Int,4),
					new SqlParameter("@yh_right_name", SqlDbType.VarChar,50)};
            parameters[0].Value = re.yh_right_id;
            parameters[1].Value = re.yh_right_name;

            DBExecuteUtil.executeSqlWithPar(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public void Delete(int yh_right_id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from yh_right ");
            strSql.Append(" where yh_right_id=@yh_right_id ");
            SqlParameter[] parameters = {
					new SqlParameter("@yh_right_id", SqlDbType.Int,4)};
            parameters[0].Value = yh_right_id;

            DBExecuteUtil.executeSqlWithPar(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public RightEntity GetModel(int yh_right_id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 yh_right_id,yh_right_name ");
            strSql.Append(" FROM yh_right ");
            strSql.Append(" where yh_right_id=@yh_right_id ");
            SqlParameter[] parameters = {
					new SqlParameter("@yh_right_id", SqlDbType.Int,4)};
            parameters[0].Value = yh_right_id;

            DataTable dt = DBExecuteUtil.querySqlTable(strSql.ToString(), parameters);
            RightEntity model = new RightEntity();
            if (dt.Rows.Count > 0)
            {
                if (dt.Rows[0]["yh_right_id"].ToString() != "")
                {
                    model.yh_right_id = int.Parse(dt.Rows[0]["yh_right_id"].ToString());
                }
                model.yh_right_name = dt.Rows[0]["yh_right_name"].ToString();
            }
            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataTable GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM yh_right ");
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" where " + strWhere);
            }
            return DBExecuteUtil.querySqlTable(strSql.ToString());
        }
         
        #endregion

    }
}
